"""
Case Type   : 防篡改
Case Name   : 三权分立打开时验证表的权限grant、revoke、with grant option
Create At   : 2022-03-11
Owner       : liu-tong-8848
Description :
    1.设置参数并重启数据库enableSeparationOfDuty=on
    2.创建用户
    3.切换用户1创建表
    4.切换用户2revoke权限
    5.切换用户1给普通用户3授权
    6.切换用户2回收权限用户3权限
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.用户2对象没有权限时，不能revoke对象的权限
    5.成功
    6.回收失败
    7.成功
History     :
"""


import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class ModifyCase(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.dbuserNode = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.user_name = 'u_security_rgtableprivilege_0007'
        self.user_privileges = {f'{self.user_name}_01': '',
                                f'{self.user_name}_02': '',
                                f'{self.user_name}_03': ''}
        self.table_name = 't_security_rgtableprivilege_0007'
        self.default_value = self.common.show_param('enableSeparationOfDuty')

    def test_security(self):
        text = '-----step1：设置参数并重启数据库enableSeparationOfDuty=on; ' \
               'expect:成功-----'
        self.logger.info(text)
        mod_msg = \
            self.primary_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          'enableSeparationOfDuty=on')
        self.logger.info(mod_msg)
        self.assertTrue(mod_msg, '执行失败:' + text)
        restart_msg = self.primary_sh.restart_db_cluster()
        self.logger.info(restart_msg)
        status = self.primary_sh.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败:' + text)

        text = '-----step2：创建用户 expect:成功-----'
        self.logger.info(text)
        for user, privilege in self.user_privileges.items():
            create_cmd = f"drop user if exists {user} cascade; " \
                f"create user {user} {privilege} " \
                f"password '{macro.PASSWD_REPLACE}' ; "
            msg = self.primary_sh.execut_db_sql(create_cmd)
            self.logger.info(msg)
            self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG and
                          self.constant.CREATE_ROLE_SUCCESS_MSG, msg,
                          '执行失败:' + text)

        text = '----step3:切换用户1创建表; expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(
            f"set role {self.user_name}_01 password '{macro.PASSWD_REPLACE}';"
            f"create table {self.user_name}_01.{self.table_name}"
            f"(id int primary key,name varchar(100));")
        self.logger.info(sql_cmd)
        self.assertIn(self.constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, sql_cmd,
                      "执行失败:" + text)

        text = '----step4:切换用户2,revoke权限; ' \
               'expect:用户2对象没有权限时，不能revoke对象的权限----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(
            f"set role {self.user_name}_02 password '{macro.PASSWD_REPLACE}';"
            f"revoke select, insert, update, delete, truncate on table "
            f"{self.user_name}_01.{self.table_name} from {self.user_name}_01;")
        self.logger.info(sql_cmd)
        self.assertIn(self.constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.constant.PERMISSION_DENIED, sql_cmd,
                      "执行失败:" + text)

        text = '----step5:切换用户1给普通用户3授权; expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(
            f"set role {self.user_name}_01 password '{macro.PASSWD_REPLACE}';"
            f"grant select,insert,update,delete,truncate on table "
            f"{self.user_name}_01.{self.table_name} to {self.user_name}_03;")
        self.logger.info(sql_cmd)
        self.assertIn(self.constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.constant.GRANT_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)

        text = '----step6:切换用户2回收用户3权限; expect:回收失败----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(
            f"set role {self.user_name}_02 password '{macro.PASSWD_REPLACE}';"
            f"revoke select,insert,update,delete,truncate on table "
            f"{self.user_name}_01.{self.table_name} from {self.user_name}_03;")
        self.logger.info(sql_cmd)
        self.assertIn(self.constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.constant.PERMISSION_DENIED, sql_cmd,
                      "执行失败:" + text)

    def tearDown(self):
        text = '----step7:清理环境 expect:成功----'
        self.logger.info(text)
        self.logger.info('恢复参数默认值')
        mod_msg1 = \
            self.primary_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f'enableSeparationOfDuty='
                                          f'{self.default_value}')
        self.logger.info(mod_msg1)
        self.assertTrue(mod_msg1, '执行失败:' + text)
        restart_msg = self.primary_sh.restart_db_cluster()
        self.logger.info(restart_msg)
        status = self.primary_sh.get_db_cluster_status()
        self.logger.info('删除用户')
        drop_msg = []
        for user in self.user_privileges.keys():
            drop_cmd = f"drop user if exists {user} cascade; "
            msg = self.primary_sh.execut_db_sql(drop_cmd)
            self.logger.info(msg)
            drop_msg.append(msg)
        self.assertEqual(3,
                         drop_msg.count(self.constant.DROP_ROLE_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败:' + text)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
